﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using com.vs.General;
using com.vs.DataAccess;

namespace com.vs.DataAccess.DO
{
    public partial class LocationDO : BaseDO
    {

        #region "Get Model"
        /// <summary>
        /// 将符合数据库主键的持久对象提取到模型对象中，如数据库符合条件的持久对象不存在，则返回空对象
        /// </summary>
        /// <remarks></remarks>
        public static LocationDO GetModelByPK(Session sess, string stLocation, string stWarehouse)
        {
            DataHelper helper = null;
            DataTable dtModel = null;
            string stSQL = "SELECT * FROM LOCATION WHERE WAREHOUSE = @WAREHOUSE AND LOCATION = @LOCATION ";
            try
            {
                helper = new DataHelper(sess);
                dtModel = helper.GetTable(CommandType.Text, stSQL, new IDataParameter[]{
                    DataHelper.BuildParameter(sess, "@LOCATION", stLocation),
                    DataHelper.BuildParameter(sess, "@WAREHOUSE", stWarehouse)
                });
                if (!DataManager.IsEmpty(dtModel))
                {
                    return GetModel(dtModel.Rows[0]);
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }

        #endregion

        #region "DataBase Operation"
        /// <summary>
        /// 在数据库里创建该数据访问对象的持久化数据
        /// </summary>
        /// <remarks></remarks>
        public int Insert(Session sess)
        {
            DataHelper helper = null;
            IDataParameter[] paras = null;
            string stSQL = "CFG_ILocation";
            try
            {
                helper = new DataHelper(sess);
                paras = ValueParas(this);
                return helper.GetInt(CommandType.StoredProcedure, stSQL, paras);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public void Delete(Session sess)
        {
            DataHelper helper = null;
            string stSQL = "DELETE FROM LOCATION WHERE WAREHOUSE = @WAREHOUSE AND LOCATION = @LOCATION";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.Text, stSQL, UpdateWherePara(this));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }

        /// <summary>
        /// 将该数据访问对象的变化同步到数据库中，如果该数据访问对象在数据库中的持久数据不存在，同步不会发生，同时也不会抛出例外
        /// </summary>
        /// <remarks></remarks>
        public void Update(Session sess)
        {
            DataHelper helper = null;
            string stSQL = "CFG_ULocation";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.StoredProcedure, stSQL, ValueParas(this));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public string Update(Session sess, Dictionary<string, object> dcData)
        {
            DataHelper helper = null;
            StringBuilder sbSQL = null;
            string stSQL = null;
            int i = 0;
            IDataParameter[] paras = null;

            try
            {
                if (dcData.Count < 1)
                    return null;
                helper = new DataHelper(sess);
                sbSQL = new StringBuilder(256)
                            .Append(" UPDATE LOCATION SET ");
                paras = new IDataParameter[dcData.Count + 2];
                foreach (KeyValuePair<string, object> kvp in dcData)
                {
                    sbSQL.Append(kvp.Key).Append(" = @Value").Append(i).Append(",");
                    paras[i] = new SqlParameter("@Value" + i, kvp.Value);
                    i++;
                }

                stSQL = sbSQL.ToString();
                stSQL = stSQL.Substring(0, stSQL.Length - 1);

                stSQL = stSQL + " WHERE LOCATION = @LOCATION AND WAREHOUSE = @WAREHOUSE";
                paras[i++] = new SqlParameter("@LOCATION", this.LOCATION);
                paras[i++] = new SqlParameter("@WAREHOUSE", this.WAREHOUSE);

                helper.Update(CommandType.Text, stSQL, paras);

                return null;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }
        #endregion

        #region "Data Load"
        /// <summary>
        /// 装载对象LOCATION的所有持久性对象到集合，如果数据量大，调用此方法会造成性能上的问题，请谨慎使用
        /// </summary>
        /// <remarks></remarks>
        public static DataTable GetAllToTable(Session sess)
        {
            DataHelper helper = null;
            string stSQL = SelectSequel;
            try
            {
                helper = new DataHelper(sess);
                return helper.GetTable(CommandType.Text, stSQL);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public static List<LocationDO> GetAll(Session sess)
        {
            DataTable dtModel = null;

            try
            {
                dtModel = GetAllToTable(sess);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        /// <summary>
        /// 装载指定列的值等于指定值的的所有持久性LOCATION对象到集合
        /// </summary>
        /// <remarks></remarks>
        public static DataTable GetTableByColumn(Session sess, string columnName, Object value)
        {
	        DataHelper helper = null;
	        IDataParameter[] paras = null;
	        string stSQL = null;

	        try
	        {
		        helper = new DataHelper(sess);
		        stSQL = SelectSequel + " Where [" + columnName + "] =@Value ";
		        paras = new IDataParameter[]{
			        new SqlParameter( "@Value", value)
		        };
		        return helper.GetTable(CommandType.Text, stSQL, paras);
	        }
	        catch (Exception e)
	        {
		        throw e;
	        }
	        finally
	        {
		        if (helper != null)
			        helper.Dispose();
	        }
        }

        public static List<LocationDO> GetListByColumn(Session sess, string columnName, Object value)
        {
            DataTable dtModel = null;
            try
            {
                dtModel = GetTableByColumn(sess, columnName, value);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }

        }


        /// <summary>
        /// 从DataView装载持久性LOCATION对象到集合
        /// </summary>
        /// <remarks></remarks>
        private static List<LocationDO> LoadListFromDataView(DataView dv)
        {
            List<LocationDO> list = new List<LocationDO>();
            for (int index = 0; index <= dv.Count - 1; index++)
            {
                list.Add(GetModel(dv[index].Row));
            }
            return list;
        }


        /// <summary>
        /// loading data value  to property from Datarow
        /// </summary>
        /// <remarks></remarks>
        private static LocationDO GetModel(DataRow row)
        {
            LocationDO locDO = new LocationDO();
            if (!row["INTERNAL_LOC_NUM"].Equals(DBNull.Value)) locDO.INTERNAL_LOC_NUM = Convert.ToInt32(row["INTERNAL_LOC_NUM"]);
            if (!row["LOCATION"].Equals(DBNull.Value)) locDO.LOCATION = Convert.ToString(row["LOCATION"]);
            if (!row["WAREHOUSE"].Equals(DBNull.Value)) locDO.WAREHOUSE = Convert.ToString(row["WAREHOUSE"]);
            if (!row["ZONE"].Equals(DBNull.Value)) locDO.ZONE = Convert.ToString(row["ZONE"]);
            if (!row["LOCATION_TYPE"].Equals(DBNull.Value)) locDO.LOCATION_TYPE = Convert.ToString(row["LOCATION_TYPE"]);
            if (!row["MULTI_ITEM"].Equals(DBNull.Value)) locDO.MULTI_ITEM = Convert.ToString(row["MULTI_ITEM"]);
            if (!row["TRACK_LPN"].Equals(DBNull.Value)) locDO.TRACK_LPN = Convert.ToString(row["TRACK_LPN"]);
            if (!row["LOCATION_STS"].Equals(DBNull.Value)) locDO.LOCATION_STS = Convert.ToString(row["LOCATION_STS"]);
            if (!row["LAST_CYCLE_COUNT_DATE"].Equals(DBNull.Value)) locDO.LAST_CYCLE_COUNT_DATE = Convert.ToDateTime(row["LAST_CYCLE_COUNT_DATE"]);
            if (!row["ACTIVE"].Equals(DBNull.Value)) locDO.ACTIVE = Convert.ToString(row["ACTIVE"]);
            if (!row["USER_STAMP"].Equals(DBNull.Value)) locDO.USER_STAMP = Convert.ToString(row["USER_STAMP"]);
            if (!row["DATE_TIME_STAMP"].Equals(DBNull.Value)) locDO.DATE_TIME_STAMP = Convert.ToDateTime(row["DATE_TIME_STAMP"]);
            if (!row["TEMPLATE_FIELD1"].Equals(DBNull.Value)) locDO.TEMPLATE_FIELD1 = Convert.ToString(row["TEMPLATE_FIELD1"]);
            if (!row["TEMPLATE_FIELD2"].Equals(DBNull.Value)) locDO.TEMPLATE_FIELD2 = Convert.ToString(row["TEMPLATE_FIELD2"]);
            if (!row["TEMPLATE_FIELD3"].Equals(DBNull.Value)) locDO.TEMPLATE_FIELD3 = Convert.ToString(row["TEMPLATE_FIELD3"]);
            if (!row["TEMPLATE_FIELD4"].Equals(DBNull.Value)) locDO.TEMPLATE_FIELD4 = Convert.ToString(row["TEMPLATE_FIELD4"]);
            if (!row["TEMPLATE_FIELD5"].Equals(DBNull.Value)) locDO.TEMPLATE_FIELD5 = Convert.ToString(row["TEMPLATE_FIELD5"]);
            if (!row["MIN_QTY_UM"].Equals(DBNull.Value)) locDO.MIN_QTY_UM = Convert.ToString(row["MIN_QTY_UM"]);
            if (!row["LOCATION_CLASS"].Equals(DBNull.Value)) locDO.LOCATION_CLASS = Convert.ToString(row["LOCATION_CLASS"]);

            return locDO;
        }

        #endregion

        #region "Other function"
        /// <summary>
        /// 该数据访问对象从数据库中提取数据的Sql语句 
        /// </summary>
        /// <remarks></remarks>
        private static string SelectSequel
        {
            get
            {
                return "Select * From [LOCATION] ";
            }
        }

        /// <summary>
        /// 该数据访问对象的属性值装载到数据库更新参数数组
        /// </summary>
        /// <remarks></remarks>
        private static IDataParameter[] ValueParas(LocationDO locDO)
        {
            SqlParameter[] paras = new SqlParameter[19];
            paras[0] = new SqlParameter("@INTERNAL_LOC_NUM", locDO.INTERNAL_LOC_NUM);
            paras[1] = new SqlParameter("@LOCATION", locDO.LOCATION);
            paras[2] = new SqlParameter("@WAREHOUSE", locDO.WAREHOUSE);
            paras[3] = new SqlParameter("@ZONE", locDO.ZONE == String.Empty ? DBNull.Value : (object)locDO.ZONE);
            paras[4] = new SqlParameter("@LOCATION_TYPE", locDO.LOCATION_TYPE == String.Empty ? DBNull.Value : (object)locDO.LOCATION_TYPE);
            paras[5] = new SqlParameter("@MULTI_ITEM", locDO.MULTI_ITEM == String.Empty ? DBNull.Value : (object)locDO.MULTI_ITEM);
            paras[6] = new SqlParameter("@TRACK_LPN", locDO.TRACK_LPN == String.Empty ? DBNull.Value : (object)locDO.TRACK_LPN);
            paras[7] = new SqlParameter("@LOCATION_STS", locDO.LOCATION_STS == String.Empty ? DBNull.Value : (object)locDO.LOCATION_STS);
            paras[8] = new SqlParameter("@LAST_CYCLE_COUNT_DATE", locDO.LAST_CYCLE_COUNT_DATE == DateTime.MinValue ? DBNull.Value : (object)locDO.LAST_CYCLE_COUNT_DATE);
            paras[9] = new SqlParameter("@ACTIVE", locDO.ACTIVE == String.Empty ? DBNull.Value : (object)locDO.ACTIVE);
            paras[10] = new SqlParameter("@USER_STAMP", locDO.USER_STAMP == String.Empty ? DBNull.Value : (object)locDO.USER_STAMP);
            paras[11] = new SqlParameter("@DATE_TIME_STAMP", locDO.DATE_TIME_STAMP == DateTime.MinValue ? DBNull.Value : (object)locDO.DATE_TIME_STAMP);
            paras[12] = new SqlParameter("@TEMPLATE_FIELD1", locDO.TEMPLATE_FIELD1 == String.Empty ? DBNull.Value : (object)locDO.TEMPLATE_FIELD1);
            paras[13] = new SqlParameter("@TEMPLATE_FIELD2", locDO.TEMPLATE_FIELD2 == String.Empty ? DBNull.Value : (object)locDO.TEMPLATE_FIELD2);
            paras[14] = new SqlParameter("@TEMPLATE_FIELD3", locDO.TEMPLATE_FIELD3 == String.Empty ? DBNull.Value : (object)locDO.TEMPLATE_FIELD3);
            paras[15] = new SqlParameter("@TEMPLATE_FIELD4", locDO.TEMPLATE_FIELD4 == String.Empty ? DBNull.Value : (object)locDO.TEMPLATE_FIELD4);
            paras[16] = new SqlParameter("@TEMPLATE_FIELD5", locDO.TEMPLATE_FIELD5 == String.Empty ? DBNull.Value : (object)locDO.TEMPLATE_FIELD5);
            paras[17] = new SqlParameter("@MIN_QTY_UM", locDO.MIN_QTY_UM == String.Empty ? DBNull.Value : (object)locDO.MIN_QTY_UM);
            paras[18] = new SqlParameter("@LOCATION_CLASS", locDO.LOCATION_CLASS == String.Empty ? DBNull.Value : (object)locDO.LOCATION_CLASS);

            paras[0].DbType = DbType.Double;
            paras[1].DbType = DbType.String;
            paras[2].DbType = DbType.String;
            paras[3].DbType = DbType.String;
            paras[4].DbType = DbType.String;
            paras[5].DbType = DbType.String;
            paras[6].DbType = DbType.String;
            paras[7].DbType = DbType.String;
            paras[8].DbType = DbType.Date;
            paras[9].DbType = DbType.String;
            paras[10].DbType = DbType.String;
            paras[11].DbType = DbType.DateTime;
            paras[12].DbType = DbType.String;
            paras[13].DbType = DbType.String;
            paras[14].DbType = DbType.String;
            paras[15].DbType = DbType.String;
            paras[16].DbType = DbType.String;
            paras[17].DbType = DbType.String;
            paras[18].DbType = DbType.String;             

            return paras;
        }

        private static IDataParameter[] UpdateWherePara(LocationDO locDO)
        {
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@WAREHOUSE", locDO.WAREHOUSE);
            paras[1] = new SqlParameter("@LOCATION", locDO.LOCATION);
            return paras;
        }

        #endregion


        public static List<LocationDO> GetListByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataTable dtModel = null;

            try
            {
                dtModel = GetTableByColumns(sess, dcParam, stOrderBy);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static DataTable GetTableByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataHelper helper = null;
            StringBuilder sbSQL = null;
            int i = 0;
            IDataParameter[] pars = null;

            try
            {
                helper = new DataHelper(sess);
                sbSQL = new StringBuilder(SelectSequel)
                    .Append(" WHERE 1=1 ");

                pars = new IDataParameter[dcParam.Count];
                foreach (KeyValuePair<string, KeyValuePair<string, object>> de in dcParam)
                {
                    sbSQL.Append(" AND ").Append(de.Key).Append(de.Value.Key).Append("@Value").Append(i);
                    pars[i] = new SqlParameter("@Value" + i, de.Value.Value);
                    i++;
                }

                if (!StringManager.IsEmpty(stOrderBy))
                {
                    sbSQL.Append(stOrderBy);
                }
                return helper.GetTable(CommandType.Text, sbSQL.ToString(), pars);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }

        public LocationDO Copy(Session sess)
        {
            LocationDO locDO = new LocationDO();
            locDO.ACTIVE = this.ACTIVE;
            locDO.DATE_TIME_STAMP = DateTime.Now;
            locDO.LAST_CYCLE_COUNT_DATE = this.LAST_CYCLE_COUNT_DATE;
            locDO.LOCATION = this.LOCATION;
            locDO.LOCATION_CLASS = this.LOCATION_CLASS;
            locDO.LOCATION_STS = this.LOCATION_STS;
            locDO.LOCATION_TYPE = this.LOCATION_TYPE;
            locDO.MIN_QTY_UM = this.MIN_QTY_UM;
            locDO.MULTI_ITEM = this.MULTI_ITEM;
            locDO.TEMPLATE_FIELD1 = this.TEMPLATE_FIELD1;
            locDO.TEMPLATE_FIELD2 = this.TEMPLATE_FIELD2;
            locDO.TEMPLATE_FIELD3 = this.TEMPLATE_FIELD3;
            locDO.TEMPLATE_FIELD4 = this.TEMPLATE_FIELD4;
            locDO.TEMPLATE_FIELD5 = this.TEMPLATE_FIELD5;
            locDO.TRACK_LPN = this.TRACK_LPN;
            locDO.USER_STAMP = sess.User;
            locDO.WAREHOUSE = this.WAREHOUSE;
            locDO.ZONE = this.ZONE;
            return locDO;
        }
    }
}
